package com.binary.coder.gift.core.common.utils;

import com.binary.coder.gift.core.common.bean.BizException;
import lombok.extern.slf4j.Slf4j;
import org.apache.commons.lang3.StringUtils;

import java.util.ArrayList;
import java.util.List;
import java.util.regex.Matcher;
import java.util.regex.Pattern;
import java.util.stream.Collectors;

@Slf4j
public class SysApiSqlUtils {

    public static List<String> kw = new ArrayList<>();

    static {
        kw.add("drop ");
        kw.add("delete ");
        kw.add("truncate ");
        kw.add("update ");
        kw.add("alter ");
        kw.add("create ");
        kw.add("grant ");
    }

    public static void valSec(String sql) throws BizException {
        sql = sql.toLowerCase();
        for (String s : kw) {
            if (sql.indexOf(s) >= 0) {
                log.error(sql);
                throw new BizException("【安全告警】:Sql预计只能进行查询,不能包含：" + s);
            }
        }
    }

    public static void main(String[] args) {
        System.out.println(getResultCell("select a.ID,substring_index(substring_index(a.mSize,',',b.help_topic_id+1),',',-1) name,eid, a / e,sum(123)\n" +
                "from tbl_name a\n" +
                "join mysql.help_topic b\n" +
                "on b.help_topic_id < (length(a.mSize) - length(replace(a.mSize,',',''))+1)\n" +
                "order by a.ID;"));
    }

    public static String clearKH(String sql) {
        char[] chars = sql.toCharArray();
        int sc = 0;// 记录数量
        for (int i = 0; i < chars.length; i++) {
            if (chars[i] == '(') {
                sc++;
                chars[i] = '\r';
            } else if (chars[i] == ')') {
                sc--;
                chars[i] = '\r';
            } else if (sc > 0) {
                chars[i] = '\r';
            }
        }
        sql = new String(chars);
        sql = sql.replace("\r", "");
        return sql;
    }

    public static String getResultCell(String sql) {
        //sql=sql.replaceAll("\\{[^}]*\\}","");
        List<String> param = getResultCellAsList(sql);
        return StringUtils.join(param, ";");
    }

    public static List<String> getResultCellAsList(String sql) {
        //sql=sql.replaceAll("\\{[^}]*\\}","");
        List<String> param = new ArrayList<>();
        sql = sql.replace("'", "").replace("`", "");
        int end = sql.toLowerCase().indexOf("from");
        int st = sql.toLowerCase().indexOf("select");
        if (end > 0 && st >= 0) {
            sql = sql.substring(st + 6, end).trim();
        }
        // 去掉函数，可能包逗号等信息。
        sql = clearKH(sql);
        for (String s : sql.split(",")) {
            s = s.trim();
            if (s.length() == 0) {
                continue;
            }
            int e1 = s.lastIndexOf(" ");//最后一个空格
            int e2 = s.lastIndexOf(".");//最后一个.
            int e = (s.split(" ").length > 1 || e1 > e2) ? e1 : e2;// 以最后出现的为准,或者由多个空格则以空格为准
            s = s.substring(e < 0 ? 0 : e + 1, s.length()).trim();
            param.add(s);
        }
        // 去重
        param = param.stream().distinct().collect(Collectors.toList());
        return param;
    }

    public static String getSqlParam(String sql) {
        List<String> param = new ArrayList<>();
        Pattern pattern = Pattern.compile("\\{([^}]*)\\}");
        Matcher matcher = pattern.matcher(sql);
        while (matcher.find()) {
            param.add(matcher.group().replace("{", "").replace("}", ""));
        }
        // 去重
        param = param.stream().distinct().collect(Collectors.toList());
        return StringUtils.join(param, ";");
    }

    public static String tryEscape(int script, String sql) {
        if (script == 1) {
            // 转义处理
            sql = sql
                    .replace("<=", "&lt;=").replace(" < ", " &lt; ")
                    .replace(">=", "&gt;=").replace(" > ", " &gt; ");
        }

        return sql;
    }

    public static String tryUnEscape(int script, String sql) {
        if (script == 1) {
            // 转义处理
            sql = sql.replace("&lt;", "<").replace("&gt;", ">");
        }

        return sql;
    }

    public static String sqlTrimChar(String str) {
        char[] cs = str.toCharArray();
        int l = cs.length - 1;
        for (; l >= 0; l--) {
            if ((cs[l] >= '0' && cs[l] <= '9')
                    || (cs[l] >= 'A' && cs[l] <= 'Z')
                    || (cs[l] >= 'a' && cs[l] <= 'z')
                    || (cs[l] == '>')
                    || (cs[l] == '}')
                    || (cs[l] == ')')
                    || (cs[l] == '\'')
                    || (cs[l] == '"')) {
                break;
            }
        }
        if (l > 0) {
            return str.substring(0, l + 1);
        } else {
            return str;
        }
    }

    public static String sqlRemoveChar(String str, char c) {
        char[] cs = str.toCharArray();
        int l = cs.length - 1;
        for (; l >= 0; l--) {
            if (!(cs[l] == ' ' || cs[l] == c)) {
                break;
            }
        }
        if (l > 0) {
            return str.substring(0, l + 1);
        } else {
            return str;
        }
    }

}
